package Test_2019_3_4.util;

import Test_2019_3_4.pojo.Dvd;
import Test_2019_3_4.pojo.User;

import java.io.InputStream;
import java.security.MessageDigest;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;

public class JdbcUtil {
    private Connection conn = null;
    private PreparedStatement pstmt = null;
    private ResultSet rs = null;
    private static String url;
    private static String user;
    private static String password;
    private static int initCount = 10;// 初始化连接数
    private static int maxCount = 50;// 最大连接数
    private static int currentCount = 0;// 当前连接数
    // 数据库连接的容器（池）
    private static List<Connection> connectionPool = new ArrayList<Connection>();

    static {
        Properties prop = new Properties();
        try {
            InputStream is = JdbcUtil.class.getClassLoader()
                    .getResourceAsStream("atbdb.properties");
            prop.load(is);
            // 注册驱动
            String driverClassName = prop.getProperty("driverClassName");
            Class.forName(driverClassName);
            url = prop.getProperty("url");
            user = prop.getProperty("username");
            password = prop.getProperty("password");
            // 初始化一定数量的数据库连接
            for (int i = 0; i < initCount; i++) {
                connectionPool.add(createConnection());
                currentCount++;
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 创建数据库连接
     */
    private static Connection createConnection() {
        Connection conn = null;
        try {
            conn = DriverManager.getConnection(url, user, password);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return conn;
    }

    /**
     * 获取数据库连接
     */
    public static synchronized Connection getConnection() throws SQLException {
        Connection conn = null;
        // 池中存在空闲连接，则获取空闲连接，并将其从池中移除
        if (connectionPool.size() > 0) {
            conn = connectionPool.get(0);
            connectionPool.remove(0);
        } else {
            if (currentCount < maxCount) {
                // 连接数没有达到最大值，创建新连接供其使用
                conn = createConnection();
                currentCount++;
            } else {
                throw new SQLException("已没有空闲的数据库连接。。。");
            }
        }
        return conn;
    }

    /**
     * 释放资源
     */
    public static void close(Connection conn, Statement stmt, ResultSet rs) {
        // 将数据库李连杰重新放置到池中（切记不可关闭）
        if (conn != null) {
            connectionPool.add(conn);
        }
        if (stmt != null) {
            try {
                stmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    /**
     * 获得PreparedStatement
     */
    private PreparedStatement getPrepareStatement(String sql) {
        Connection conn = createConnection();
        PreparedStatement pstmt = null;
        try {
            pstmt = conn.prepareStatement(sql);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return pstmt;
    }

    /*--------------------- 用户 开始 -------------------------*/

    /**
     * 将数据库中用户信息转为集合
     */
    private List<User> queryUser() {
        List<User> list = new ArrayList<User>();
        String sql = "select *from User";
        pstmt = getPrepareStatement(sql);
        try {
            rs = pstmt.executeQuery();
            User bd = null;
            while (rs.next()) {
                bd = new User();
                bd.setEmail(rs.getString("email"));
                bd.setPassword(rs.getString("password"));
                list.add(bd);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }

    /**
     * 用户注册
     */
    public int addUser(User user) {
        int rlt = 0;
        List<User> list = queryUser();
        for (User ls : list) {
            if (ls.getEmail().equals(user.getEmail())) {
                return rlt;
            }
        }
        String sql = "insert into User(email,password) values(?,?)";
        pstmt = getPrepareStatement(sql);
        try {
            pstmt.setString(1, user.getEmail());
            pstmt.setString(2, MD5(user.getPassword()));
            rlt = pstmt.executeUpdate();
        } catch (SQLException e) {
            System.out.println(e.getMessage());
            e.printStackTrace();
        }
        return rlt;
    }

    /**
     * 判断输入账号密码是否正确
     */
    public int isReally(String name, String password) {
        int rlt = 0;
        List<User> list = queryUser();
        for (User ls : list) {
            if (ls.getEmail().equals(name) && ls.getPassword().equals(MD5(password))) {
                return 1;
            }
        }
        return rlt;
    }

    /**
     * 修改冻结状态
     */
    public void updateState(String state, String username) {
        try {
            String sql = "update user SET state = ? where email = ?";
            pstmt = getPrepareStatement(sql);
            Object[] params = {state, username};
            for (int i = 1; i <= params.length; i++) {
                pstmt.setObject(i, params[i - 1]);
            }
            pstmt.executeUpdate();
        } catch (SQLException e) {
            System.out.println(e.getMessage());
            e.printStackTrace();
        }
    }

    /**
     * 将数据库中用户信息转为集合
     */
    public String selectState(String email) {
        String sql = "select *from User where email = '" + email + "'";
        pstmt = getPrepareStatement(sql);
        User bd = null;
        try {
            rs = pstmt.executeQuery();
            while (rs.next()) {
                bd = new User();
                bd.setEmail(rs.getString("email"));
                bd.setPassword(rs.getString("password"));
                bd.setState(rs.getString("state"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return bd.getState();
    }

    /**
     * 修改密码
     */
    public int updateUser(String name, String password) {
        int rlt = 0;
        try {
            String sql = "update user SET password = ? where email = ?";
            pstmt = getPrepareStatement(sql);
            Object[] params = {MD5(password), name};
            for (int i = 1; i <= params.length; i++) {
                pstmt.setObject(i, params[i - 1]);
            }
            rlt = pstmt.executeUpdate();
        } catch (SQLException e) {
            System.out.println(e.getMessage());
            e.printStackTrace();
        }
        return rlt;
    }

    /**
     * 判断账户是否存在
     */
    public int judge(String name) {
        int rlt = 0;
        List<User> list = queryUser();
        for (User ls : list) {
            if (ls.getEmail().equals(name)) {
                return 1;
            }
        }
        return rlt;
    }

    /**
     * MD5加密
     */
    private String MD5(String inStr) {
        MessageDigest md5 = null;
        try {
            md5 = MessageDigest.getInstance("MD5");
        } catch (Exception e) {
            System.out.println(e.toString());
            e.printStackTrace();
            return "";
        }
        char[] charArray = inStr.toCharArray();
        byte[] byteArray = new byte[charArray.length];

        for (int i = 0; i < charArray.length; i++)
            byteArray[i] = (byte) charArray[i];

        byte[] md5Bytes = md5.digest(byteArray);

        StringBuffer hexValue = new StringBuffer();

        for (int i = 0; i < md5Bytes.length; i++) {
            int val = ((int) md5Bytes[i]) & 0xff;
            if (val < 16)
                hexValue.append("0");
            hexValue.append(Integer.toHexString(val));
        }
        return hexValue.toString();
    }

    /*--------------------- 用户 结束 -------------------------*/

    /*--------------------- Dvd 开始 -------------------------*/

    /**
     * 将dvd数据库中用户信息转为集合
     */
    private List<Dvd> queryDvd() {
        List<Dvd> list = new ArrayList<Dvd>();
        String sql = "select *from dvd";
        try {
            pstmt = getPrepareStatement(sql);
            ResultSet rs = pstmt.executeQuery();
            while (rs.next()) {
                Dvd bd;
                bd = new Dvd();
                bd.setNo(rs.getInt("no"));
                bd.setName(rs.getString("name"));
                bd.setTime(rs.getString("time"));
                bd.setAuthor(rs.getString("author"));
                list.add(bd);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }

    /**
     * 添加图书
     */
//    public int addDvd(Dvd dvd) {
//        int rlt = 0;
//        List<Dvd> list = queryDvd();
//        for (Dvd ls : list) {
//            if (ls.getName().equals(dvd.getName())) {
//                return rlt;
//            }
//        }
//        String sql = "insert into dvd(name,state,image) values(?,?,?)";
//        try {
//            pstmt = getPrepareStatement(sql);
//            pstmt.setString(1, dvd.getName());
//            pstmt.setInt(2, dvd.getState());
//            pstmt.setString(3, dvd.getImage());
//            rlt = pstmt.executeUpdate();
//        } catch (SQLException e) {
//            System.out.println(e.getMessage());
//            e.printStackTrace();
//        }
//        return rlt;
//    }

    /**
     * 删除dvd操作
     */
    public int deleteDvd(int no) {
        int rlt = 0;
        try {
            String sql = "DELETE FROM dvd where no = ?";
            pstmt = getPrepareStatement(sql);
            pstmt.setObject(1, no);
            rlt = pstmt.executeUpdate();
        } catch (SQLException e) {
            System.out.println(e.getMessage());
            e.printStackTrace();
        }
        return rlt;
    }

    /**
     * 初始状态
     */
    public List<Dvd> find(int page) {
        List<Dvd> list = new ArrayList<Dvd>();
        String sql = "SELECT *FROM dvd LIMIT " + (page - 1) * Dvd.PAGE_SIZE + "," + Dvd.PAGE_SIZE + "";
        try {
            pstmt = getPrepareStatement(sql);
            rs = pstmt.executeQuery();
            while (rs.next()) {
                Dvd bd;
                bd = new Dvd();
                bd.setNo(rs.getInt("no"));
                bd.setName(rs.getString("name"));
                bd.setTime(rs.getString("time"));
                bd.setAuthor(rs.getString("author"));
                list.add(bd);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }

    /**
     * 模糊查询
     */
    public List<Dvd> findPage(int page, String search) {
        List<Dvd> list = new ArrayList<Dvd>();
        String sql = "SELECT *FROM dvd " +
                "WHERE no like '%" + search + "%' " +
                "or name like '%" + search + "%' " +
                "or author like '%" + search + "%' " +
                "LIMIT " + (page - 1) * Dvd.PAGE_SIZE + "," + Dvd.PAGE_SIZE + "";
        try {
            pstmt = getPrepareStatement(sql);
            rs = pstmt.executeQuery();
            Dvd bd;
            while (rs.next()) {
                bd = new Dvd();
                bd.setNo(rs.getInt("no"));
                bd.setName(rs.getString("name"));
                bd.setTime(rs.getString("time"));
                bd.setAuthor(rs.getString("author"));
                list.add(bd);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }

    /**
     * 总DVD数量
     */
    public int findCount() {
        int count = 0;
        String sql = "select count(*) from dvd";
        try {
            pstmt = getPrepareStatement(sql);
            rs = pstmt.executeQuery();
            if (rs.next()) {
                count = rs.getInt(1);
            }
        } catch (Exception e) {
            // TODO 自动生成的 catch 块
            e.printStackTrace();
        }
        return count;
    }

    /**
     * 模糊查询DVD数量
     */
    public int findCountPage(String search) {
        int count = 0;
        String sql = "select count(*) from dvd " +
                "WHERE no like '%" + search + "%' " +
                "or name like '%" + search + "%' " +
                "or author like '%" + search + "%'";
        try {
            pstmt = getPrepareStatement(sql);
            rs = pstmt.executeQuery();
            if (rs.next()) {
                count = rs.getInt(1);
            }
        } catch (Exception e) {
            // TODO 自动生成的 catch 块
            e.printStackTrace();
        }
        return count;
    }

    /*--------------------- Dvd 结束 -------------------------*/
}
